DROP TABLE IF EXISTS jms_dm.dm_service_site_out_agg_dt;
CREATE EXTERNAL TABLE jms_dm.dm_service_site_out_agg_dt (
    site_in_date date COMMENT '入库日期',
    province string COMMENT '省份',
    city string COMMENT '城市',
    service_site_cnt bigint COMMENT '服务点数',
    site_in_cnt bigint COMMENT '入库量',
    site_out_cnt bigint COMMENT '出库量',
    site_abnormal_out_cnt bigint COMMENT '异常出库量',
    site_current_out_cnt bigint COMMENT '当日出库量',
    site_current_end_cnt bigint COMMENT '当日完结量',
    site_t1_out_cnt bigint COMMENT 'T+1日出库量',
    site_t1_end_cnt bigint COMMENT 'T+1日完结量',
    site_t3_out_cnt bigint COMMENT 'T+3日出库量',
    site_t3_end_cnt bigint COMMENT 'T+3日完结量',
    site_t6_out_cnt bigint COMMENT 'T+6日出库量',
    site_t6_end_cnt bigint COMMENT 'T+6日完结量'
)
    comment  '百世出入库统计汇总报表'
    partitioned by (dt string comment '时间分区')
    stored  as parquet
    location '/dw/hive/jms_dm.db/external/dm_service_site_out_agg_dt'
    tblproperties (
        'discover.partitions'= 'false',
        'parquet.column.index.access'= 'true'
        );


DROP TABLE IF EXISTS jms_dm.dm_service_site_out_brand_agg_dt;
CREATE EXTERNAL TABLE jms_dm.dm_service_site_out_brand_agg_dt (
  site_in_date date COMMENT '入库日期',
  province string COMMENT '省份',
  city string COMMENT '城市',
  expresscompanycode string COMMENT '快递公司编码',
  site_in_cnt bigint COMMENT '入库量',
  site_out_cnt bigint COMMENT '出库量',
  site_abnormal_out_cnt bigint COMMENT '异常出库量',
  site_current_out_cnt bigint COMMENT '当日出库量',
  site_current_end_cnt bigint COMMENT '当日完结量',
  site_t1_out_cnt bigint COMMENT 'T+1日出库量',
  site_t1_end_cnt bigint COMMENT 'T+1日完结量',
  site_t3_out_cnt bigint COMMENT 'T+3日出库量',
  site_t3_end_cnt bigint COMMENT 'T+3日完结量',
  site_t6_out_cnt bigint COMMENT 'T+6日出库量',
  site_t6_end_cnt bigint COMMENT 'T+6日完结量'
)
    comment  '百世出入库统计品牌汇总报表'
    partitioned by (dt string comment '时间分区')
    stored  as parquet
    location '/dw/hive/jms_dm.db/external/dm_service_site_out_brand_agg_dt'
    tblproperties (
        'discover.partitions'= 'false',
        'parquet.column.index.access'= 'true'
        );

----add jixk 20230418
alter table jms_dm.dm_service_site_out_station_agg_dt add COLUMNS(
delivery_site_in_cnt   bigint  comment '送货上门入库量',
delivery_site_out_cnt  bigint  comment '送货上门出库量'
)
;

alter table jms_dm.dm_service_site_out_agg_dt add columns (
             area_code string comment '区县code'
            , area string comment '区县名字'
    )cascade;